﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WebPromenadebyenTabeller;
using System.Data.SqlClient;
using System.Configuration;

namespace PromPersonerDB
{
    public static class PromGatewayPersoner
    {

        const String CONNECTION_STRING = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PromDatabase.mdf;Integrated Security=True;User Instance=True";
     
        public static List<Personer> FindAllePersoner()
        {
            List<Personer> nyListe = new List<Personer>();

            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;
            
            con.Open();

            System.Data.IDbCommand command = con.CreateCommand();
            command.CommandText = "SELECT * FROM PERSONER";
            System.Data.IDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                int lejlighedsID = int.Parse(reader["lejlighedsID"].ToString());
                string navn = reader["navn"].ToString();
                string adresse = reader["adresse"].ToString();
                int postnr = int.Parse(reader["postnr"].ToString());
                string by = reader["bynavn"].ToString();
                int telefonnr = int.Parse(reader["telefonnr"].ToString());
                int mobilnr = int.Parse(reader["mobilnr"].ToString());
                string email = reader["email"].ToString();
                int status = int.Parse(reader["status"].ToString());
                string nyPassword = reader["password"].ToString();

                Personer nyPerson = new Personer(lejlighedsID, navn, adresse, postnr, by, telefonnr, mobilnr, email, status, nyPassword );

                nyListe.Add(nyPerson);

            }

            con.Close();
            return nyListe;
        }

        public static Personer OpretPerson(int lejlighedsID, string navn, string adresse, int postnr, string by, int telefonnr, int mobilnr, string email, int status)
        {
            Personer nyPerson = null;

            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;
            
            con.Open();

            System.Data.IDbCommand command = con.CreateCommand();
            command.CommandText = "INSERT INTO PERSONER "
                                    + "(lejlighedsID, navn, adresse, postnr, bynavn, telefonnr, mobilnr, email, status) VALUES " +
                                    "(" + lejlighedsID + ", '" + navn + "', '" + adresse + "', " + postnr + ", '" + by + "', " + telefonnr + ", " + mobilnr + ", '" + email + "', " + status + ")";
            

            
            int antalIndsat = command.ExecuteNonQuery();
            if (antalIndsat == 1)


            {
                command.CommandText = "SELECT @@identity AS lastinsertedkey";
                string slastkey = command.ExecuteScalar().ToString();
                int lastkey = int.Parse(slastkey);
                command.CommandText = "SELECT * FROM PERSONER "
                                       + " WHERE (PERSONID=" + lastkey + ")";
                System.Data.IDataReader reader = command.ExecuteReader();
                if (reader.Read())
                {
                    int nyPersonID = int.Parse(reader["personID"].ToString());
                    int nyLejlighedsID = int.Parse(reader["lejlighedsID"].ToString());
                    string nytNavn = reader["navn"].ToString();
                    string nyAdresse = reader["adresse"].ToString();
                    int nytPostnr = int.Parse(reader["postnr"].ToString());
                    string nyBy = reader["bynavn"].ToString();
                    int nytTelefonnr = int.Parse(reader["telefonnr"].ToString());
                    int nytMobilnr = int.Parse(reader["mobilnr"].ToString());
                    string nyEmail = reader["email"].ToString();
                    int nyStatus = int.Parse(reader["status"].ToString());
                    string nyPassword = reader["password"].ToString();

                    nyPerson = new Personer(nyLejlighedsID, nytNavn, nyAdresse, nytPostnr, nyBy, nytTelefonnr, nytMobilnr, nyEmail, nyStatus, nyPassword);

                }
            }
            con.Close();

            return nyPerson;
        }

        public static void OpdaterPerson(int personID, int lejlighedsID, string navn, string adresse, int postnr, string by, int telefonnr, int mobilnr, string email, int status)
        {
            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);


            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;

            con.Open();

            System.Data.IDbCommand command = con.CreateCommand();
            command.CommandText = "UPDATE PERSONER SET " + "lejlighedsID=" + lejlighedsID + " " + ", navn='" + navn + "', adresse='" + adresse + "',telefonnr=" + telefonnr + ", mobilnr=" + mobilnr + ", email='" + email + "', status=" + status + " " + "WHERE personID=" + personID;
            int antalOpdateret = command.ExecuteNonQuery();

            con.Close();
        }

        public static Personer FindPerson(int personID)
        {
            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;

            con.Open();

            System.Data.IDbCommand command = con.CreateCommand();

            command.CommandText = "SELECT * FROM PERSONER WHERE (personID =" + personID + " )";
            System.Data.IDataReader reader = command.ExecuteReader();
            Personer nyPerson = null;
            if (reader.Read())
            {

            int nyPersonID = int.Parse(reader["personID"].ToString());
            int nyLejlighedsID = int.Parse(reader["lejlighedsID"].ToString());
            string nytNavn = reader["navn"].ToString();
            string nyAdresse = reader["adresse"].ToString();
            int nytPostnr = int.Parse(reader["postnr"].ToString());
            string nyBy = reader["bynavn"].ToString();
            int nytTelefonnr = int.Parse(reader["telefonnr"].ToString());
            int nytMobilnr = int.Parse(reader["mobilnr"].ToString());
            string nyEmail = reader["email"].ToString();
            int nyStatus = int.Parse(reader["status"].ToString());
            string nyPassword = reader["password"].ToString();

            nyPerson = new Personer(nyLejlighedsID, nytNavn, nyAdresse, nytPostnr, nyBy, nytTelefonnr, nytMobilnr, nyEmail, nyStatus, nyPassword);
            }
            con.Close();
            return nyPerson;
        }


        public static Personer FindEmail(string email)
        {
            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;

            con.Open();

            System.Data.IDbCommand command = con.CreateCommand();

            command.CommandText = "SELECT * FROM PERSONER WHERE (email ='" + email + "' )";
            
            System.Data.IDataReader reader = command.ExecuteReader();
            reader.Read();

            int nyPersonID = int.Parse(reader["personID"].ToString());
            int nyLejlighedsID = int.Parse(reader["lejlighedsID"].ToString());
            string nytNavn = reader["Password"].ToString(); //password!!!
            string nyAdresse = reader["adresse"].ToString();
            int nytPostnr = int.Parse(reader["postnr"].ToString());
            string nyBy = reader["bynavn"].ToString();
            int nytTelefonnr = int.Parse(reader["telefonnr"].ToString());
            int nytMobilnr = int.Parse(reader["mobilnr"].ToString());
            string nyEmail = reader["email"].ToString();
            int nyStatus = int.Parse(reader["status"].ToString());
            string nyPassword = reader["password"].ToString();

            


            Personer nyPerson = new Personer(nyLejlighedsID, nytNavn, nyAdresse, nytPostnr, nyBy, nytTelefonnr, nytMobilnr, nyEmail, nyStatus, nyPassword);

            con.Close();
          
            return nyPerson;
        }

        public static Personer FindStatus(string status)
        {
            //SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
            con.ConnectionString = CONNECTION_STRING;

            con.Open();

            System.Data.IDbCommand command = con.CreateCommand();

            command.CommandText = "SELECT * FROM PERSONER WHERE (status ='" + status + "' )";

            System.Data.IDataReader reader = command.ExecuteReader();
            reader.Read();

            int nyPersonID = int.Parse(reader["personID"].ToString());
            int nyLejlighedsID = int.Parse(reader["lejlighedsID"].ToString());
            string nytNavn = reader["Password"].ToString(); //password!!!
            string nyAdresse = reader["adresse"].ToString();
            int nytPostnr = int.Parse(reader["postnr"].ToString());
            string nyBy = reader["bynavn"].ToString();
            int nytTelefonnr = int.Parse(reader["telefonnr"].ToString());
            int nytMobilnr = int.Parse(reader["mobilnr"].ToString());
            string nyEmail = reader["email"].ToString();
            int nyStatus = int.Parse(reader["status"].ToString());
            string nyPassword = reader["password"].ToString();




            Personer nyPerson = new Personer(nyLejlighedsID, nytNavn, nyAdresse, nytPostnr, nyBy, nytTelefonnr, nytMobilnr, nyEmail, nyStatus, nyPassword);

            con.Close();

            return nyPerson;
        }




    }
}
